DROP SEQUENCE IF EXISTS user_id_seq;
CREATE SEQUENCE user_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS vacancy_id_seq;
CREATE SEQUENCE vacancy_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS applicant_id_seq;
CREATE SEQUENCE applicant_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS customer_id_seq;
CREATE SEQUENCE customer_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS contact_person_id_seq;
CREATE SEQUENCE contact_person_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS requisite_id_seq;
CREATE SEQUENCE requisite_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS region_id_seq;
CREATE SEQUENCE region_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS role_id_seq;
CREATE SEQUENCE role_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS target_date_id_seq;
CREATE SEQUENCE target_date_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;
DROP SEQUENCE IF EXISTS level_salary_id_seq;
CREATE SEQUENCE level_salary_id_seq
  INCREMENT 10
  MINVALUE 10
  MAXVALUE 9223372036854775807
  START 10
  CACHE 1;

GRANT ALL ON TABLE user_id_seq TO public;
GRANT ALL ON TABLE vacancy_id_seq TO public;
GRANT ALL ON TABLE applicant_id_seq TO public;
GRANT ALL ON TABLE customer_id_seq TO public;
GRANT ALL ON TABLE contact_person_id_seq TO public;
GRANT ALL ON TABLE requisite_id_seq TO public;
GRANT ALL ON TABLE region_id_seq TO public;
GRANT ALL ON TABLE role_id_seq TO public;
GRANT ALL ON TABLE target_date_id_seq TO public;
GRANT ALL ON TABLE level_salary_id_seq TO public;

DROP TABLE IF EXISTS "user";
CREATE TABLE "user"
(
  id bigint NOT NULL DEFAULT nextval(('user_id_seq'::text)::regclass),
  "name" text,
  CONSTRAINT user_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
DROP TABLE IF EXISTS "applicant";
CREATE TABLE "applicant"
(
  id bigint NOT NULL DEFAULT nextval(('applicant_id_seq'::text)::regclass),
  "title" text,
  CONSTRAINT applicant_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE "role"
(
   id bigint NOT NULL DEFAULT nextval(('role_id_seq'::text)::regclass),
   "title" text,
   CONSTRAINT role_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE "region"
(
   id bigint NOT NULL DEFAULT nextval(('region_id_seq'::text)::regclass),
   "title" text,
   CONSTRAINT region_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE "requisite"
(
   id bigint NOT NULL DEFAULT nextval(('requisite_id_seq'::text)::regclass),
   "legaladdress" text,
   "iin" text,
   "kpp" text,
   "accountnumber" integer,
   "bankname" text,
   "bankidcode" text,
   "correspondentaccount" text,
   "supervisor" text,
   "supervisorposition" text,
   "chiefaccountant" text,
   CONSTRAINT requisite_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE "customer"
(
    id bigint NOT NULL DEFAULT nextval(('customer_id_seq'::text)::regclass),
    "number" text,
    "codename" text,
    "officialname" text,
    "address" text,
    "phone" text,
    "fax" text,
    "email" text,
    "website" text,
    "region_id" bigint DEFAULT NULL,
    "requisite_id" bigint DEFAULT NULL,
    CONSTRAINT customer_pk PRIMARY KEY (id),
    CONSTRAINT "region_id_fk" FOREIGN KEY ("region_id") REFERENCES region(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT "requisite_id_fk" FOREIGN KEY ("requisite_id") REFERENCES requisite(id)
    ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
CREATE TABLE "contact_person"
(
    id bigint NOT NULL DEFAULT nextval(('contact_person_id_seq'::text)::regclass),
    "number" text,
    "fullname" text,
    "sex" integer,
    "datebirth" date,
    "workphone" text,
    "mobilephone" text,
    "email" text,
    "additionalemail" text,
    "main" boolean,
    "position" text,
    "datelastcontact" date,
    "customer_id" bigint DEFAULT NULL,
  CONSTRAINT contact_person_pk PRIMARY KEY (id),
  CONSTRAINT "customer_id_fk" FOREIGN KEY ("customer_id") REFERENCES customer(id)
  ON UPDATE CASCADE ON DELETE SET NULL
)
WITH (OIDS=FALSE);
CREATE TABLE "target_date" (
  id bigint NOT NULL DEFAULT nextval(('target_date_id_seq'::text)::regclass),
  "count" integer DEFAULT NULL,
  "time" integer DEFAULT NULL,
  "closeup" date DEFAULT NULL,
  CONSTRAINT target_date_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE "level_salary" (
  id bigint NOT NULL DEFAULT nextval(('level_salary_id_seq'::text)::regclass),
  "_from" float8,
  "_to" float8,
  CONSTRAINT level_salary_pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE "vacancy"
(
  id bigint NOT NULL DEFAULT nextval(('vacancy_id_seq'::text)::regclass),
  "number" text DEFAULT NULL,
  "title" text DEFAULT NULL,
  "customer_id" bigint DEFAULT NULL,
  "datepublish" timestamp DEFAULT NULL,
  "appearance" integer DEFAULT NULL,
  "target_date_id" bigint DEFAULT NULL,
  "state" integer DEFAULT NULL,
  "closingdate" timestamp DEFAULT NULL,
  "categoryselection" integer DEFAULT NULL,
  "complexity" integer DEFAULT NULL,
  "priority" integer DEFAULT NULL,
  "salary" real,
  "level_salary_id" bigint DEFAULT NULL,
  "specialty" text DEFAULT NULL,
  "applicant_id" bigint DEFAULT NULL,
  "channelselection" integer DEFAULT NULL,
  "additionalinformation" text DEFAULT NULL,
  CONSTRAINT vacancy_pk PRIMARY KEY (id),
  CONSTRAINT "applicant_id_fk" FOREIGN KEY ("applicant_id") REFERENCES applicant (id)
  ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT "customer_id_fk" FOREIGN KEY ("customer_id") REFERENCES customer (id)
  ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT "target_date_id_fk" FOREIGN KEY ("target_date_id") REFERENCES target_date(id)
  ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
CREATE TABLE "customer_contact_person" (
  "customer_id" bigint NOT NULL,
  "contact_person_id" bigint NOT NULL,
  PRIMARY KEY ("customer_id","contact_person_id"),
  UNIQUE ("contact_person_id"),
  CONSTRAINT "customer_id_fk" FOREIGN KEY ("customer_id") REFERENCES customer (id)
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "contact_person_id_fk" FOREIGN KEY ("contact_person_id") REFERENCES contact_person (id)
  ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
CREATE TABLE "contact_person_role" (
  "contact_person_id" bigint NOT NULL,
  "role_id" bigint NOT NULL,
  PRIMARY KEY ("contact_person_id","role_id"),
  CONSTRAINT "role_id_fk" FOREIGN KEY ("role_id") REFERENCES role (id)
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "contact_person_id_fk" FOREIGN KEY ("contact_person_id") REFERENCES contact_person (id)
  ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
CREATE TABLE "vacancy_contact_person" (
  "contact_person_id" bigint NOT NULL,
  "vacancy_id" bigint NOT NULL,
  PRIMARY KEY ("contact_person_id","vacancy_id"),
  CONSTRAINT "vacancy_id_fk" FOREIGN KEY ("vacancy_id") REFERENCES vacancy (id)
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "contact_person_id_fk" FOREIGN KEY ("contact_person_id") REFERENCES contact_person (id)
  ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);

ALTER TABLE "user" OWNER TO webmodule;
ALTER TABLE "applicant" OWNER TO webmodule;
ALTER TABLE "vacancy" OWNER TO webmodule;
ALTER TABLE "customer" OWNER TO webmodule;
ALTER TABLE "role" OWNER TO webmodule;
ALTER TABLE "region" OWNER TO webmodule;
ALTER TABLE "requisite" OWNER TO webmodule;
ALTER TABLE "contact_person" OWNER TO webmodule;
ALTER TABLE "target_date" OWNER TO webmodule;
ALTER TABLE "level_salary" OWNER TO webmodule;
ALTER TABLE "contact_person_role" OWNER TO webmodule;
ALTER TABLE "customer_contact_person" OWNER TO webmodule;
ALTER TABLE "vacancy_contact_person" OWNER TO webmodule;

/*CREATE OR REPLACE FUNCTION process_deleting_customer_link() RETURNS TRIGGER AS $body$
    BEGIN
        DELETE FROM customer_contact_person WHERE customer_id=old.id;
        RETURN old;
    END;
$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION process_deleting_contact_person_link() RETURNS TRIGGER AS $body$
    BEGIN
        DELETE FROM contact_person_role WHERE contact_person_id=old.id;
        RETURN old;
    END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER pre_deleting_customer BEFORE DELETE
    ON customer FOR EACH ROW EXECUTE PROCEDURE process_deleting_customer_link();

CREATE TRIGGER pre_deleting_contact_person BEFORE DELETE
    ON contact_person FOR EACH ROW EXECUTE PROCEDURE process_deleting_contact_person_link();
*/